﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using GenUtil.Util;
using GenUtil;
using System.Data.Common;
using System.Data;

namespace DataLayer.Dao.Sql
{
    public class BasicDaoSql
    {
        private static Logger logger = new Logger(typeof(BasicDaoSql));

        public static string SCHEMA = "dbo";
        string ConnectionString = Settings.MyDatabase.ConnectionString;
        string ProviderName = Settings.MyDatabase.ProviderName;

        protected string getProviderName()
        {
            return ProviderName;
        }

        protected DbConnection getDatabaseConnection()
        {
            logger.debug("Entering in BasicDaoDb2sp.getDatabaseConnection");
            DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
            DbConnection con = factory.CreateConnection();
            con.ConnectionString = ConnectionString;
            con.Open();
            logger.debug("Ending of BasicDaoDb2sp.getDatabaseConnection");
            return con;
        }

        protected string getParameterString(string para)
        {
            string result = "";

            switch (ProviderName)
            {
                case "System.Data.SqlClient":
                    result = "@" + para;
                    break;

                case "System.Data.OleDb":
                    result = "?";
                    break;
            }
            return result;
        }

        protected DbCommand CreateDBCommand(DbConnection con, String sqlQuery)
        {
            DbCommand pstmt = null;
            pstmt = con.CreateCommand();
            pstmt.CommandTimeout = 1200;
            pstmt.CommandText = sqlQuery;
            pstmt.CommandType = CommandType.Text;
            return pstmt;
        }

        protected DbParameter[] CreateDBParameter(DbCommand cStmt, int paramNo)
        {
            DbParameter[] para = new DbParameter[paramNo];
            for (int j = 0; j < paramNo; j++)
                para[j] = cStmt.CreateParameter();

            return para;
        }

        protected void PopulateDBParameter(DbParameter para, String fieldName, System.Object value)
        {
            para.ParameterName = getParameterString(fieldName.Trim());
            System.Object tempValue = value;
            if (value is DateTime)
            {
                DateTime obj = (DateTime)value;
                tempValue = getProperDate(obj);
            }
            else if (value == null)
            {
                tempValue = System.DBNull.Value;
            }
            para.Value = tempValue;
        }

        protected void PopulateDBParameterToUpper(DbParameter para, String fieldName, string value)
        {
            para.ParameterName = getParameterString(fieldName.Trim());
            object tempValue = value;
            if (value == null)
            {
                tempValue = System.DBNull.Value;
            }
            else
            {
                tempValue = value.ToUpper();
            }
            para.Value = tempValue;
        }

        protected object getProperDate(DateTime para)
        {
            if (para != DateTime.MinValue)
                return para;
            else
                return System.DBNull.Value;
        }

        protected int getIntValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? 0 : Convert.ToInt32(rs[fieldName.Trim()]));
        }

        protected Decimal getDecimalValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? 0 : Convert.ToDecimal(rs[fieldName.Trim()]));
        }

        protected short getShortValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? (short)0 : Convert.ToInt16(rs[fieldName.Trim()]));
        }

        protected double getDoubleValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? 0 : Convert.ToDouble(rs[fieldName.Trim()]));
        }

        protected string getStringValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? "" : Convert.ToString(rs[fieldName.Trim()]));
        }

        protected DateTime getDateTimeValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? DateTime.MinValue : Convert.ToDateTime(rs[fieldName.Trim()]));
        }

        protected TimeSpan getTimeStampValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? TimeSpan.MinValue : (TimeSpan)rs[fieldName.Trim()]);
        }

        protected long getLongValue(DbDataReader rs, String fieldName)
        {
            return (rs[fieldName.Trim()] == System.DBNull.Value ? (long)0 : Convert.ToInt64(rs[fieldName.Trim()]));
        }

        protected string addQueryArray(string fieldName, string[] values)
        {
            string newQuery = "";
            for (int i = 0; i < values.Length; i++)
            {
                if (i == 0)
                {
                    newQuery += " " + fieldName + " in (";
                }
                newQuery += "'" + values[i] + "'";
                if (i == values.Length - 1)
                {
                    newQuery += ")";
                }
                else
                {
                    newQuery += ",";
                }
            }
            return newQuery;
        }

        public void AddRange(DbCommand pstmt, DbParameter[] para)
        {
            for (int i = 0; i < para.Length; i++)
            {
                pstmt.Parameters.Add(para[i]);
            }
        }

    }
}
